#################################################################################
#
# Copyright (c) 2006 Nathan Collins
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation; either version 2
# of the License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
#################################################################################

#################################################################################
#
#	Author:
#		Nathan Collins <npcollins@gmail.com>
#
#################################################################################

"""Handles queries to the database.

Returns results from MySQLdb queries to Python dicts.
"""

# imports
import MySQLdb
from dbinfo import getdbinfo
import types
from threading import Semaphore

# globals
__version__ = '0.1'
__all__ = ['dbquery','dbquerysafe','dbselect','dbselectsafe','dbinsert','dbinsertsafe','dbdelete','dbdeletesafe']

# db connnection
_dbconn = None

# semaphore
_sema = Semaphore(1)

# functions
def dbquery(querystring, values = None):
	"""
	Performs the query passed in querystring. Returns any results as a dict.
	"""
	# access dbconn
	global _dbconn
	global _sema

	# convert values to utf-8
	utfvalues = []
	if values != None:
		for v in values:
			if types.UnicodeType == type(v):
				utfvalues.append( v.encode('utf-8') )
			else:
				utfvalues.append( v )

	# secure mysql connection
	_sema.acquire()

	# if no connection is present, create a new one
	if _dbconn is None:
		# get db connection info
		dbinfo = getdbinfo()
		# connect to db
		_dbconn = MySQLdb.connect(host = dbinfo['host'],
					user = dbinfo['user'],
					passwd = dbinfo['passwd'],
					db = dbinfo['db'] )
	# perform query
	cursor = _dbconn.cursor()
	
	if values == None:
		# plain query
		rownum = cursor.execute(querystring)
	else:
		# safe query
		rownum = cursor.execute(querystring, utfvalues)

	# no rows means no return
	if rownum > 0:
		rows = cursor.fetchall()
		cols = cursor.description

	# release mysql connection
	_sema.release()

	if rownum < 1:
		return None

	# if there is nothing to return, then return now
	if cols == None: return

	# put results into a dict
	keys = [col[0] for col in cols]

	queryresults = []

	for row in rows:
		queryresults.append( dict(zip(keys,row)) )

	return queryresults


def dbquerysafe(querystring, values):
	"""
	Performs the query pass in querystring, safely inserting variables from
	the values list into querystring whereever %s is found.
	"""
	return dbquery(querystring, values)


def dbselect(tablename, wherestring = '1>0', columns = ['*'], values = ()):
	"""
	Select fields from tablename with limitation from wherestring and
	return them as a list of dicts.
	"""
	# check columns format and convert to list if needed
	if columns is types.TupleType: columns = list(columns)
	if columns in types.StringTypes: columns = [columns]

	columnstring = ','.join(columns)

	querystring = 'SELECT %s FROM %s WHERE %s;' % (columnstring,tablename,wherestring)

	# if variables needing to be escaped were passed, do safe query
	if len(values) > 0: return dbquerysafe(querystring, values)
	# otherwise, just do a plain query
	return dbquery(querystring)

def dbselectsafe(tablename, wherestring, values, columns = ['*']):
	"""
	Performs a select query where values are automatically escaped. The
	wherestring should leave an unquoted %s where the variable should be,
	then pass all variable in a tuple as the third argument. Column selection
	is optional as the fourth argument.
	"""
	return dbselect(tablename, wherestring, columns, values)


def dbinsert(tablename, insertdict, onduplicate = ''):
	"""
	Insert records into tablename where keys from insertdict are the column
	names and the values are row values.
	"""
	# split up the dict
	keys = []
	vals = []

	for key,val in insertdict.iteritems():
		keys.append(key)
		vals.append(val)

	# prepare values for query
	columns = ','.join(keys)
	values = ['%s' for i in range( len(vals) )]
	values = ','.join(values)

	# create query
	querystring = 'INSERT INTO %s (%s) VALUES (%s)' % (tablename,columns,values)

	# duplicate key update (optional)
	if onduplicate != '':
		querystring += ' ON DUPLICATE KEY ' + onduplicate

	querystring += ';'

	# perform query
	dbquerysafe( querystring, tuple(vals) )


def dbinsertsafe(tablename, insertdict, onduplicate = ''):
	"""
	Same as dbinsert as dbinsert is also safe. This is just here to so there
	won't be any confusion (or possibly more confusion) on why there is no
	safe version of insert. In reality, there is only one insert and it's
	safe... at least it should be.
	"""
	dbinsert(tablename, insertdict, onduplicate)


def dbdelete(tablename, wherestring, values = ()):
	"""
	Delete rows from tablename with limitation from wherestring.
	"""
	
	querystring = 'DELETE FROM %s WHERE %s;' % (tablename, wherestring)

	# if variables needing to be escaped were passed, do safe query
	if len(values) > 0: return dbquerysafe(querystring, values)
	# otherwise, just do a plain query
	return dbquery(querystring)


def dbdeletesafe(tablename, wherestring, values):
	"""
	Delete rows from tablename. User input may be passed in as a tuple of values.
	The wherestring should leave an unquoted %s where the variables are to be
	inserted into the query.
	"""
	return dbdelete(tablename, wherestring, values)


# eof
